package DB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBspel {
	private static String dbURL = "jdbc:derby://localhost:1527/myDB;create=true;user=me;password=mine";
	private static String tableName = "spel";
	private static Connection conn = null;
	private static Statement stmt = null;

	/* /// main //////////////////////////////////////// */
	public static void main(String[] args) {
		createConnection();
		createStructure();
		shutdown();
	}

	/* /// createStructure //////////////////////////////////////// */
	public static void createStructure() {
		try {
			stmt = conn.createStatement();
			try {
				stmt.executeUpdate("DROP TABLE " + tableName);
				stmt.executeUpdate("DROP TABLE score ");

			} catch (Exception e) {
				System.out.println("An error has occured while creating database, please contact the administrator");
			}

			stmt.executeUpdate("CREATE TABLE spel "
					+ "( id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY, " + " naam VARCHAR(50) ,"
					+ " email VARCHAR(50) ," + " map INTEGER ," + " score INTEGER )");
			try {
				stmt.executeUpdate("CREATE TABLE score " + "( id INTEGER NOT NULL, " + " lvl INTEGER ,"
						+ " tijd INTEGER ," + " aantalTorens INTEGER ," + " basisHealth INTEGER ,"
						+ " scorePunten INTEGER ," + " geld INTEGER ," + " kills INTEGER )");

				// stmt.executeUpdate( "ALTER TABLE spel " +
				// "ADD FOREIGN KEY (id) REFERENCES score(id);");
				stmt.close();
			} catch (Exception e) {
				System.out.println("An error has occured while creating database, please contact the administrator");
			}
		} catch (Exception e) {
			System.out.println("An error has occured while creating database, please contact the administrator");
		}
	}

	/* /// createConnection //////////////////////////////////////// */
	public static void createConnection() {
		try {
			Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
			// Get a connection
			conn = DriverManager.getConnection(dbURL);
		} catch (Exception except) {
			except.printStackTrace();
		}
	}

	/* /// getBest //////////////////////////////////////// */
	public static String[][] getBest(int map) {
		String arr[][] = new String[10][4];

		try {
			stmt = conn.createStatement();
			ResultSet results = stmt.executeQuery("SELECT naam, email, map, score FROM spel " + "WHERE map = " + map
					+ " ORDER BY score DESC");
			int i = 0;
			while (results.next() && i < 10) {
				arr[i][0] = "" + results.getString(1);
				arr[i][1] = "" + results.getString(2);
				arr[i][2] = "" + results.getInt(3);
				arr[i][3] = "" + results.getInt(4);
				i++;
			}

			return arr;
		} catch (Exception e) {
			System.out.println("Error while fetching data best player");
			e.printStackTrace();
			return null;
		}

	}

	/* /// getBestScore //////////////////////////////////////// */
	public static int getBestScore(int map) {
		try {
			stmt = conn.createStatement();
			ResultSet results = stmt.executeQuery("SELECT score FROM spel " + "WHERE map = " + map + " AND score IN ( "
					+ "SELECT max( score ) FROM spel GROUP BY map )");
			int retVal = 0;
			while (results.next()) {
				retVal = results.getInt(1);
			}
			return retVal;
		} catch (Exception e) {
			System.out.println("Error while fetching data best player");
			e.printStackTrace();
			return 0;
		}

	}

	/* /// select //////////////////////////////////////// */
	public static void select() {
		try {
			stmt = conn.createStatement();
			ResultSet results = stmt.executeQuery("select * from " + tableName);
			while (results.next()) {
				int id = results.getInt(1);
				String naam = results.getString(2);
				String email = results.getString(3);
				String map = results.getString(4);
				String score = results.getString(5);
				System.out.println(id + "\t" + naam + "\t\t\t" + email + "\t" + map + "\t" + score);
			}
			results.close();

			results = stmt.executeQuery("SELECT * FROM score ");

			while (results.next()) {
				int id = results.getInt(1);
				int lvl = results.getInt(2);
				int tijd = results.getInt(3);
				int aantalTorens = results.getInt(4);
				int basisHealth = results.getInt(5);
				int scorePunten = results.getInt(6);
				int geld = results.getInt(7);
				int kills = results.getInt(8);
				System.out.println(id + "\t" + lvl + "\t\t\t" + tijd + "\t" + aantalTorens + "\t\t" + basisHealth
						+ "\t" + scorePunten + "\t" + geld + "\t" + kills);
			}
			results.close();

			stmt.close();
		} catch (Exception e) {
		}

	}

	/* /// insertIntoSpel //////////////////////////////////////// */
	public static void insertIntoSpel(String naam, String email, int map, int score) {
		try {
			stmt = conn.createStatement();
			stmt.execute("INSERT INTO " + tableName + " (naam, email, map, score ) " + "VALUES ( '" + naam + "','"
					+ email + "'," + map + "," + score + ")");
			stmt.close();
		} catch (SQLException sqlExcept) {
			System.out.println("An error has occured while inserting into spel");
		}
	}

	/* /// insertIntoScore //////////////////////////////////////// */
	public static void insertIntoScore(int level, int tijd, int aantalTorens, int basisHealth, int scorePunten,
			int geld, int kills) {
		try {
			stmt = conn.createStatement();
			// maken dat hij automatisch het id fetcht
			int id = 0;
			ResultSet results = stmt.executeQuery("SELECT max(id) FROM spel");
			while (results.next()) {
				id = results.getInt(1);
			}
			stmt.execute("INSERT INTO score ( id, lvl, tijd, aantalTorens, basisHealth, scorePunten, geld, kills ) "
					+ "VALUES ( " + id + ", " + level + "," + tijd + "," + aantalTorens + "," + basisHealth + ","
					+ scorePunten + "," + geld + "," + kills + ")");
			stmt.close();
		} catch (Exception e) {
			System.out.println("An error has occured while inserting into scores");
			e.printStackTrace();
		}
	}

	/* /// shutdown //////////////////////////////////////// */
	public static void shutdown() {
		try {
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				DriverManager.getConnection(dbURL + ";shutdown=true");
				conn.close();
			}
		} catch (SQLException sqlExcept) {
		}
	}

}